﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DTO_MechSysWeb;

namespace DAL_MechSysWeb
{
    public class DALCurriculo : DALBase
    {
        private DTOCurriculo CriarCurriculo(SqlDataReader sqlDataReader)
        {
            try
            {
                DTOCurriculo dtoCurriculo = new DTOCurriculo();

                if (!string.IsNullOrEmpty(sqlDataReader["CurriculoID"].ToString()))
                {
                    dtoCurriculo.CurriculoID = (int)sqlDataReader["CurriculoID"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Nome"].ToString()))
                {
                    dtoCurriculo.Nome = (string)sqlDataReader["Nome"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Sobrenome"].ToString()))
                {
                    dtoCurriculo.Sobrenome = (string)sqlDataReader["Sobrenome"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Sexo"].ToString()))
                {
                    dtoCurriculo.Sexo = Convert.ToChar(sqlDataReader["Sexo"]);
                }

                if (!string.IsNullOrEmpty(sqlDataReader["CPF"].ToString()))
                {
                    dtoCurriculo.CPF = (string)sqlDataReader["CPF"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["EstadoCivil"].ToString()))
                {
                    dtoCurriculo.EstadoCivil = (int)sqlDataReader["EstadoCivil"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["DataNascimento"].ToString()))
                {
                    dtoCurriculo.DataNascimento = (DateTime)sqlDataReader["DataNascimento"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Telefone"].ToString()))
                {
                    dtoCurriculo.Telefone = (string)sqlDataReader["Telefone"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Celular"].ToString()))
                {
                    dtoCurriculo.Celular = (string)sqlDataReader["Celular"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Email"].ToString()))
                {
                    dtoCurriculo.Email = (string)sqlDataReader["Email"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["ImagemPerfil"].ToString()))
                {
                    dtoCurriculo.ImagemPerfil = (byte[])sqlDataReader["ImagemPerfil"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["ImagemTipo"].ToString()))
                {
                    dtoCurriculo.ImagemTipo = (string)sqlDataReader["ImagemTipo"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["EnderecoID"].ToString()))
                {
                    dtoCurriculo.EnderecoID = (int)sqlDataReader["EnderecoID"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Facebook"].ToString()))
                {
                    dtoCurriculo.Facebook = (string)sqlDataReader["Facebook"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Linkedin"].ToString()))
                {
                    dtoCurriculo.Linkedin = (string)sqlDataReader["Linkedin"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Escolaridade"].ToString()))
                {
                    dtoCurriculo.Escolaridade = (int)sqlDataReader["Escolaridade"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["AreaInteresse"].ToString()))
                {
                    dtoCurriculo.AreaInteresse = (string)sqlDataReader["AreaInteresse"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Qualificacao"].ToString()))
                {
                    dtoCurriculo.Qualificacao = (string)sqlDataReader["Qualificacao"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Objetivo"].ToString()))
                {
                    dtoCurriculo.Objetivo = (string)sqlDataReader["Objetivo"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Local"].ToString()))
                {
                    dtoCurriculo.Local = (string)sqlDataReader["Local"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["MesInicio"].ToString()))
                {
                    dtoCurriculo.MesInicio = (string)sqlDataReader["MesInicio"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["AnoInicio"].ToString()))
                {
                    dtoCurriculo.AnoInicio = (string)sqlDataReader["AnoInicio"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["MesFim"].ToString()))
                {
                    dtoCurriculo.MesFim = (string)sqlDataReader["MesFim"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["AnoFim"].ToString()))
                {
                    dtoCurriculo.AnoFim = (string)sqlDataReader["AnoFim"];
                }

                if (!string.IsNullOrEmpty(sqlDataReader["Descricao"].ToString()))
                {
                    dtoCurriculo.Descricao = (string)sqlDataReader["Descricao"];
                }             

                return dtoCurriculo;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public void Inserir(DTOCurriculo dtoCurriculo)
        {
            try
            {
                string storedProssidere = "sp_Web_InserirCurriculo";
                SqlParameter[] parameters =
                
                {      
                   new SqlParameter("@nome", dtoCurriculo.Nome),
                   new SqlParameter("@sobrenome", dtoCurriculo.Sobrenome),
                   new SqlParameter("@sexo", dtoCurriculo.Sexo),
                   new SqlParameter("@cpf", dtoCurriculo.CPF),
                   new SqlParameter("@estadoCivil", dtoCurriculo.EstadoCivil),
                   new SqlParameter("@dataNascimento", dtoCurriculo.DataNascimento),
                   new SqlParameter("@telefone", dtoCurriculo.Telefone),                       
                   new SqlParameter("@celular", dtoCurriculo.Celular),                  
                   new SqlParameter("@email", dtoCurriculo.Email),              
                   new SqlParameter("@enderecoID", dtoCurriculo.EnderecoID),                  
                   new SqlParameter("@imagemPerfil", dtoCurriculo.ImagemPerfil),
                   new SqlParameter("@imagemTipo", dtoCurriculo.ImagemTipo),
                   new SqlParameter("@facebook", dtoCurriculo.Facebook),
                   new SqlParameter("@linkedin", dtoCurriculo.Linkedin),
                   new SqlParameter("@escolaridade", dtoCurriculo.Escolaridade),
                   new SqlParameter("@areaInteresse", dtoCurriculo.AreaInteresse),
                   new SqlParameter("@qualificacao", dtoCurriculo.Qualificacao),
                   new SqlParameter("@objetivo", dtoCurriculo.Objetivo),
                   new SqlParameter("@local", dtoCurriculo.Local),
                   new SqlParameter("@mesInicio", dtoCurriculo.MesInicio),
                   new SqlParameter("@anoInicio", dtoCurriculo.AnoInicio),
                   new SqlParameter("@mesFim", dtoCurriculo.MesFim),
                   new SqlParameter("@anoFim", dtoCurriculo.AnoFim),
                   new SqlParameter("@descricao", dtoCurriculo.Descricao),
                   new SqlParameter("@dataRegistro", DateTime.Now)

                };
                ExecuteNonQuery(storedProssidere, parameters);

            }

            catch (SqlException)
            {
                throw;
            }
        }

        public void Excluir(int curriculoID)
        {
            try
            {
                string storedProssidere = "sp_Web_ExcluirCurriculo";
                SqlParameter[] parameters =

                        {                    
                           new SqlParameter("@curriculoID", curriculoID)                                    
                        };
                ExecuteNonQuery(storedProssidere, parameters);

            }

            catch (SqlException)
            {
                throw;
            }
        }

        public DTOCurriculo BuscarCurriculoPorCurriculoID(int curriculoID)
        {
            DTOCurriculo dtoCurriculo = null;

            try
            {
                string storedProssidere = "sp_Web_BuscarCurriculoPorCurriculoID";
                SqlParameter[] parameters =
                        {                    
                           new SqlParameter("@curriculoID", curriculoID)                                    
                        };
                using (SqlDataReader sqlDataReader = ExecuteReader(storedProssidere, parameters))
                {
                    if (sqlDataReader.Read())
                    {

                        dtoCurriculo = CriarCurriculo(sqlDataReader);

                    }
                }
            }
            catch (SqlException)
            {
                throw;
            }

            return dtoCurriculo;
        }

        public DTOCurriculo BuscarCurriculoImagemPorCurriculoID(int curriculoID)
        {
            DTOCurriculo dtoCurriculo = null;

            try
            {
                string storedProssidere = "SELECT CurriculoID, ImagemPerfil, ImagemTipo FROM Curriculo WHERE CurriculoID = "+curriculoID+"";
              
                using (SqlDataReader sqlDataReader = ExecuteReaderQuery(storedProssidere))
                {
                    if (sqlDataReader.Read())
                    {
                        dtoCurriculo = new DTOCurriculo();

                        if (!string.IsNullOrEmpty(sqlDataReader["CurriculoID"].ToString()))
                        {
                            dtoCurriculo.CurriculoID = (int)sqlDataReader["CurriculoID"];
                        }
                        if (!string.IsNullOrEmpty(sqlDataReader["ImagemPerfil"].ToString()))
                        {
                            dtoCurriculo.ImagemPerfil = (byte[])sqlDataReader["ImagemPerfil"];
                        }

                        if (!string.IsNullOrEmpty(sqlDataReader["ImagemTipo"].ToString()))
                        {
                            dtoCurriculo.ImagemTipo = (string)sqlDataReader["ImagemTipo"];
                        }
                    }
                }
            }
            catch (SqlException)
            {
                throw;
            }

            return dtoCurriculo;
        }


        public List<DTOCurriculo> BuscarCurriculosRecebidos()
        {
            List<DTOCurriculo> lstCurriculos = new List<DTOCurriculo>();

            try
            {
                string storedProssidere = "sp_Web_BuscarCurriculosRecebidos";

                using (SqlDataReader sqlDataReader = ExecuteReader(storedProssidere))
                {
                    while (sqlDataReader.Read())
                    {
                        DTOCurriculo dtoCliente = CriarCurriculo(sqlDataReader);
                        //CurriculoID, Nome, Sobrenome, Sexo, CPF, DataNascimento, Email, Facebook, Linkedin
                        lstCurriculos.Add(dtoCliente);

                    }
                }
            }
            catch (SqlException)
            {
                throw;
            }

            return lstCurriculos;
        }

        public int BuscarNumCurriculosRecebidos()
        {
            int numCurriculos = 0;

            try
            {
                string query = "SELECT Count(*) as NumRegistros FROM Curriculo";

                using (SqlDataReader sqlDataReader = ExecuteReaderQuery(query))
                {
                    if (sqlDataReader.Read())
                    {
                        numCurriculos = Int32.Parse(sqlDataReader["NumRegistros"].ToString());
                    }
                }

                return numCurriculos;

            }
            catch (Exception)
            {
                throw;
            }
        }      
    }
}
